<?php
namespace app\backman\controller;
use app\backman\model\Meal;
use \app\common\controller\AuthBack;
use \app\common\model\Category;
use \app\common\model\Detection;
use app\common\model\Comment;
use \app\common\model\Goods;
use \app\common\model\Customer;
use \app\common\model\Order;
use think\Db;
use think\facade\App;
require_once App::getRootPath().'/extend/PHPExcel/PHPExcel.php';

class Rate extends AuthBack{


    public function initialize(){
        parent::initialize();
    }

    public function index(){
        $type = 1;
		$where = [];
        if( !empty(input('like')) ){
	        $where[] = ['c.customer_name', 'like', "%".input('like')."%"];
        }
        //查询产品分类
		if( $type == 1 ){
			$totalNum = Detection::alias('d')
				->join('customer c','c.id = d.customer_id')
				->where($where)->group('c.id')->count();
		} else {
			$totalNum = Detection::alias('d')
				->join('goods g','g.id = d.goods_id')
				->where($where)
				->group('d.goods_id')->count();
		}

        if(request()->isAjax()){
            $page = isset($_GET['page']) ? $_GET['page'] : '1';
            $limit = isset($_GET['limit']) ? $_GET['limit'] : '20';
            $startNum = ($page - 1) * $limit;
            $totalPage = ceil($totalNum/$limit);

	        if( $type == 1 ){
		        $list = Detection::alias('d')
			            ->join('customer c','c.id = d.customer_id')
			            ->where($where)
			            ->group('c.id')
			            ->field('c.customer_sn as sn, c.customer_name as name, c.id, sum(d.count_num) as c_num')
			            ->order('c_num desc')
			            ->limit($startNum.','.$limit)
			            ->select();
	        } else {
		        $list = Detection::alias('d')
				        ->join('goods g','g.id = d.goods_id')
				        ->where($where)
				        ->group('d.goods_id')
				        ->field('g.good_sn as sn, g.title as name, g.id,sum(d.count_num) as c_num')
				        ->order('c_num desc')
				        ->limit($startNum.','.$limit)
				        ->select();
	        }
	        
            if(empty($list)){
                return json(['code'=>0,'data'=>[],'total'=>$totalPage,'count'=>$totalNum]);
            }elseif($page > $totalPage){
                return json(['code'=>0,'data'=>[],'total'=>$totalPage+1,'count'=>$totalNum]);
            }else{

                $nList = array();
                if(!empty($list)){
                    foreach($list as $k=>$vo){
                        $nList[$k] = $vo;
                        $nList[$k]['sn'] = $vo['sn'];
                        $nList[$k]['name'] = $vo['name'];
	                    $nList[$k]['c_num'] = $vo['c_num'];
	                    if( $type == 1 ){
		                    $nList[$k]['res_num'] = Db::name('order')->where('customer_id',$vo['id'])->where('status',1)->count();
	                    }else{
		                    $nList[$k]['res_num'] = Db::name('order')->where('goods_id',$vo['id'])->where('status',1)->count();
	                    }

	                    if( $type == 1 ){
		                    $nList[$k]['res_num'] = Order::where('customer_id',$vo['id'])->count();
	                    } else {
		                    $nList[$k]['res_num'] = Order::where('goods_id',$vo['id'])->count();
	                    }
	                    $nList[$k]['res_mark'] = (int)$nList[$k]['c_num'] - (int)$nList[$k]['res_num'];
	                    $nList[$k]['op'] = url('option',['id'=>$vo['id']]);
                    }
                }
                $return = [
                    'code'=>0,
                    'msg'=>'',
                    'count'=>$totalNum,
                    'data'=>$nList
                ];

                return json($return);
            }
        }else{
            $this->assign('totalNum',$totalNum);
            return view();
        }
    }


    public function product(){
	    $type = 2;
	    $where = [];
	    if( !empty(input('like')) ){
		    $where[] = ['g.title', 'like', "%".input('like')."%"];
	    }
	    //查询产品分类
	    if( $type == 1 ){
		    $totalNum = Detection::alias('d')
			    ->join('customer c','c.id = d.customer_id')
			    ->where($where)->group('c.id')->count();
	    } else {
		    $totalNum = Detection::alias('d')
			    ->join('goods g','g.id = d.goods_id')
			    ->where($where)
			    ->group('d.goods_id')->count();

	    }

	    if(request()->isAjax()){
		    $page = isset($_GET['page']) ? $_GET['page'] : '1';
		    $limit = isset($_GET['limit']) ? $_GET['limit'] : '20';
		    $startNum = ($page - 1) * $limit;
		    $totalPage = ceil($totalNum/$limit);

		    if( $type == 1 ){
			    $list = Detection::alias('d')
				    ->join('customer c','c.id = d.customer_id')
				    ->where($where)
				    ->group('c.id')
				    ->field('c.customer_sn as sn, c.customer_name as name, c.id, sum(d.count_num) as c_num')
				    ->order('c_num desc')
				    ->limit($startNum.','.$limit)
				    ->select();
		    } else {
			    $list = Detection::alias('d')
				    ->join('goods g','g.id = d.goods_id')
				    ->where($where)
				    ->group('d.goods_id')
				    ->field('g.good_sn as sn, g.title as name, g.id,sum(d.count_num) as c_num')
				    ->order('c_num desc')
				    ->limit($startNum.','.$limit)
				    ->select();
		    }

		    if(empty($list)){
			    return json(['code'=>0,'data'=>[],'total'=>$totalPage,'count'=>$totalNum]);
		    }elseif($page > $totalPage){
			    return json(['code'=>0,'data'=>[],'total'=>$totalPage+1,'count'=>$totalNum]);
		    }else{

			    $nList = array();
			    if(!empty($list)){
				    foreach($list as $k=>$vo){
					    $nList[$k] = $vo;
					    $nList[$k]['sn'] = $vo['sn'];
					    $nList[$k]['name'] = $vo['name'];
					    $nList[$k]['c_num'] = $vo['c_num'];
					    if( $type == 1 ){
						    $nList[$k]['res_num'] = Db::name('order')->where('customer_id',$vo['id'])->where('status',1)->count();
					    }else{
						    $nList[$k]['res_num'] = Db::name('order')->where('goods_id',$vo['id'])->where('status',1)->count();
					    }

					    if( $type == 1 ){
						    $nList[$k]['res_num'] = Order::where('customer_id',$vo['id'])->count();
					    } else {
						    $nList[$k]['res_num'] = Order::where('goods_id',$vo['id'])->count();
					    }
					    $nList[$k]['res_mark'] = (int)$nList[$k]['c_num'] - (int)$nList[$k]['res_num'];
					    $nList[$k]['op'] = url('option',['id'=>$vo['id']]);
				    }
			    }
			    $return = [
				    'code'=>0,
				    'msg'=>'',
				    'count'=>$totalNum,
				    'data'=>$nList
			    ];

			    return json($return);
		    }
	    }else{


		    $this->assign('totalNum',$totalNum);
		    return view();
	    }
    }




    public function lists(){
    	$data = $this->data;
    	$limit = 5;
		if( $data['type'] == 1 ){
			$count = Db::name('detection')->where('customer_id',$data['id'])->count();
			$name = '产品';
		}else {
			$count = Db::name('detection')->where('goods_id',$data['id'])->count();
			$name = '业务员';
		}

		if( request()->isAjax() ){
			if( $data['type'] == 1 ){
				$list = Db::name('detection')->alias('d')
						->join('goods g','g.id = d.goods_id')
						->where('customer_id',$data['id'])
						->field('g.good_sn as sn,g.title as name,d.id,d.count_num')
						->paginate($limit)
						->toArray();
			}else {
				$list = Db::name('detection')->alias('d')
							->join('customer c', 'c.id = d.customer_id')
							->where('goods_id',$data['id'])
							->field('c.customer_sn as sn,c.customer_name as name,d.id,d.count_num')
							->paginate($limit)
							->toArray();
			}

			$nList = array();
			if(!empty($list)){
				foreach($list['data'] as $k=>$vo){
					$nList[$k] = $vo;
					$nList[$k]['res_num'] = Db::name('order')->where('detection_id',$vo['id'])->where('status',1)->count();
					$nList[$k]['res_mark'] = (int)$nList[$k]['count_num'] - (int)$nList[$k]['res_num'];
				}
			}
			$return = [
				'code'=>0,
				'msg'=>'',
				'count'=>$count,
				'data'=>$nList
			];

			return json($return);
		}
	    $this->assign('name',$name);
	    $this->assign('id',$data['id']);
	    $this->assign('type',$data['type']);
	    $this->assign('count',$count);
    	return view();
    }


	/**

	 * 数据导出 Excel

	 */

	public function load_excel(){

		$type = 1;
		$where = [];
		if( !empty(input('type')) ){
			$type = input('type',1);
		}
		if( !empty(input('like')) ){
			$where[] = ['c.customer_name', 'like', "%".input('like')."%"];
		}

		if( $type == 1 ){
			$list = Detection::alias('d')
				->join('customer c','c.id = d.customer_id')
				->where($where)
				->group('c.id')
				->field('c.customer_sn as sn, c.customer_name as name, c.id, sum(d.count_num) as c_num')
				->order('c_num desc')
				->select();

			$hText = '业务员';
		} else {
			$list = Detection::alias('d')
				->join('goods g','g.id = d.goods_id')
				->where($where)
				->group('d.goods_id')
				->field('g.good_sn as sn, g.title as name, g.id,sum(d.count_num) as c_num')
				->order('c_num desc')
				->select();

			$hText = '产品';
		}

		if(!empty($list)){
			foreach($list as $k=>$vo){
				$nList[$k] = $vo;
				$nList[$k]['sn'] = $vo['sn'];
				$nList[$k]['name'] = $vo['name'];
				$nList[$k]['c_num'] = $vo['c_num'];
				if( $type == 1 ){
					$nList[$k]['res_num'] = Db::name('order')->where('customer_id',$vo['id'])->where('status',1)->count();
				}else{
					$nList[$k]['res_num'] = Db::name('order')->where('goods_id',$vo['id'])->where('status',1)->count();
				}

				if( $type == 1 ){
					$nList[$k]['res_num'] = Order::where('customer_id',$vo['id'])->count();
				} else {
					$nList[$k]['res_num'] = Order::where('goods_id',$vo['id'])->count();
				}
				$nList[$k]['res_mark'] = (int)$nList[$k]['c_num'] - (int)$nList[$k]['res_num'];
			}
		}
		$excelName = $hText.'库存'.date('YmdHis');
		$this->exportExcel($nList,$excelName,$hText);


		exit();


	}



	public function exportExcel($data, $name, $hText){
		$objPHPExcel = new \PHPExcel();  //实例化  相当于在桌面新建一个excel
		// 表头
		$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', $hText.'编号')
			->setCellValue('B1', $hText.'名称')
			->setCellValue('C1', '分配数量')
			->setCellValue('D1', '核销数量')
			->setCellValue('E1', '剩余数量');
		//设置内容水平居中
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 设置个表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(24);
		//设置单元格自动宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
		// 内容
		for ($i = 0, $len = count($data); $i < $len; $i++) {
			$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $data[$i]['sn']);       //业务员/产品 编号
			$objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), $data[$i]['name']);     // 业务员/产品 名称
			$objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $data[$i]['c_num']);    // 分配数量
			$objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 2), $data[$i]['res_num']);  // 核销数量
			$objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 2), $data[$i]['res_mark']); // 剩余数量
			//设置内容水平居中
			$objPHPExcel->setActiveSheetIndex(0)->getStyle($i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$objPHPExcel->getActiveSheet()->getStyle('J' . ($i + 2))->getAlignment()->setWrapText(true);
		}
		$objPHPExcel->getActiveSheet()->setTitle('User');
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean();//清楚缓存区，解决乱码问题
		header("Pragma:public");
		header("Content-Type:application/x-msexecl;name=\"{$name}.xlsx\"");
		header("Content-Disposition:inline;filename=\"{$name}.xlsx\"");
		$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
		$objWriter->save('php://output');
		exit;
	}


	/**

	 * 数据导出 Excel

	 */

	public function lists_excel(){

		$data = $this->data;

		if( $data['type'] == 1 ){
			$list = Db::name('detection')->alias('d')
				->join('goods g','g.id = d.goods_id')
				->where('customer_id',$data['id'])
				->field('g.good_sn as sn,g.title as name,d.id,d.count_num')
				->select();
			$hText = '产品';
		}else {
			$list = Db::name('detection')->alias('d')
				->join('customer c', 'c.id = d.customer_id')
				->where('goods_id',$data['id'])
				->field('c.customer_sn as sn,c.customer_name as name,d.id,d.count_num')
				->select();
			$hText = '业务员';
		}

		$nList = array();
		if(!empty($list)){
			foreach($list as $k=>$vo){
				$nList[$k] = $vo;
				$nList[$k]['res_num'] = Db::name('order')->where('detection_id',$vo['id'])->where('status',1)->count();
				$nList[$k]['res_mark'] = (int)$nList[$k]['count_num'] - (int)$nList[$k]['res_num'];
			}
		}
		$excelName = $hText.'库存明细'.date('YmdHis');
		$this->listsExcel($nList,$excelName,$hText);


		exit();


	}



	public function listsExcel($data, $name, $hText){
		$objPHPExcel = new \PHPExcel();  //实例化  相当于在桌面新建一个excel
		// 表头
		$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', $hText.'编号')
			->setCellValue('B1', $hText.'名称')
			->setCellValue('C1', '分配数量')
			->setCellValue('D1', '已核销数量')
			->setCellValue('E1', '剩余数量');
		//设置内容水平居中
		$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 设置个表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(24);
		//设置单元格自动宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
		// 内容
		for ($i = 0, $len = count($data); $i < $len; $i++) {
			$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $data[$i]['sn']);       //业务员/产品 编号
			$objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), $data[$i]['name']);     // 业务员/产品 名称
			$objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $data[$i]['count_num']);    // 分配数量
			$objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 2), $data[$i]['res_num']);  // 核销数量
			$objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 2), $data[$i]['res_mark']); // 剩余数量
			//设置内容水平居中
			$objPHPExcel->setActiveSheetIndex(0)->getStyle($i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$objPHPExcel->getActiveSheet()->getStyle('J' . ($i + 2))->getAlignment()->setWrapText(true);
		}
		$objPHPExcel->getActiveSheet()->setTitle('User');
		$objPHPExcel->setActiveSheetIndex(0);
		ob_end_clean();//清楚缓存区，解决乱码问题
		header("Pragma:public");
		header("Content-Type:application/x-msexecl;name=\"{$name}.xlsx\"");
		header("Content-Disposition:inline;filename=\"{$name}.xlsx\"");
		$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
		$objWriter->save('php://output');
		exit;
	}






}